package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.controller.ProcurementSupplierPaymentRecordController
import com.zxy.supplier_system.server.entity.GoodsUnit
import com.zxy.supplier_system.server.entity.Procurement
import com.zxy.supplier_system.server.entity.ProcurementItem
import com.zxy.supplier_system.server.entity.ProcurementReceiptItem
import com.zxy.supplier_system.server.entity.ProcurementSupplier
import com.zxy.supplier_system.server.entity.SystemService
import com.zxy.supplier_system.server.utils.SpecificationUtils
import com.zxy.supplier_system.server.utils.joinOnce
import jakarta.persistence.EntityManager
import jakarta.persistence.PersistenceContext
import jakarta.persistence.criteria.JoinType
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.domain.Specification
import org.springframework.data.jpa.repository.JpaRepository
import java.math.BigDecimal
import java.time.OffsetDateTime
import java.time.YearMonth
import java.time.ZoneId

@Suppress("FunctionName")
interface ProcurementItemRepository : JpaRepository<ProcurementItem, Int>, CustomProcurementItemRepository {

    fun findAllByProcurement_IdOrderBySequenceAsc(procurement_id: Int, pageable: Pageable): Page<ProcurementItem>

    fun existsByProcurement_IdAndGoods_Id(procurement_id: Int, goods_id: Int): Boolean

    fun existsByProcurement_IdAndGoods_Barcode(procurement_id: Int, goods_barcode: String): Boolean

    fun findByProcurement_IdAndGoods_Id(procurement_id: Int, goods_id: Int): ProcurementItem?

    fun findFirstByProcurement_Supplier_IdAndGoods_IdOrderByProcurement_CreatedDateTimeDesc(
        market_id: Int,
        goods_id: Int
    ): ProcurementItem?

    fun findFirstByProcurement_Supplier_IdAndGoods_IdAndSubGoods_IdOrderByProcurement_CreatedDateTimeDesc(
        market_id: Int,
        goods_id: Int,
        subGoods_id: Int
    ): ProcurementItem?

    fun findByProcurement_IdAndGoods_IdAndSubGoods_Id(
        marketOrder_id: Int,
        goods_id: Int,
        subGoods_id: Int?
    ): ProcurementItem?

    object Specifications {
        fun procurementSubmitted(): Specification<ProcurementItem> {
            return Specification { root, query, cb ->
                cb.isNotNull(
                    root.joinOnce<ProcurementItem, Procurement>("procurement").get<OffsetDateTime>("submittedDateTime")
                )
            }
        }

        fun systemServiceEqual(systemService: SystemService): Specification<ProcurementItem> {
            return SystemServiceRepository.Specifications.systemServiceEquals({ root ->
                root.joinOnce<ProcurementItem, Procurement>("procurement").joinOnce("supplier")
            }, systemService)
        }

        fun supplierIdEqual(supplierId: Int?): Specification<ProcurementItem> {
            return if (supplierId == null) SpecificationUtils.empty()
            else Specification { root, query, cb ->
                cb.equal(
                    root.joinOnce<ProcurementItem, Procurement>("procurement")
                        .joinOnce<Procurement, ProcurementSupplier>("supplier").get<Int>("id"), supplierId
                )
            }
        }

        fun createdDateTimeBefore(dateTime: OffsetDateTime?): Specification<ProcurementItem> {
            return if (dateTime == null) SpecificationUtils.empty()
            else Specification { root, query, cb ->
                cb.lessThanOrEqualTo(
                    root.joinOnce<ProcurementItem, Procurement>("procurement")
                        .get<OffsetDateTime>("createdDateTime"),
                    dateTime
                )
            }
        }

        fun createdDateTimeLessThanOrEqual(yearMonth: YearMonth?): Specification<ProcurementItem> {
            return if (yearMonth == null) SpecificationUtils.empty()
            else Specification { root, query, cb ->
                cb.lessThan(
                    root.joinOnce<ProcurementItem, Procurement>("procurement")
                        .get<OffsetDateTime>("createdDateTime"),
                    yearMonth.atEndOfMonth().plusDays(1).atStartOfDay()
                        .atOffset(OffsetDateTime.now(ZoneId.systemDefault()).offset)
                )
            }
        }

        fun createdDateTimeGreatThanOrEqual(yearMonth: YearMonth?): Specification<ProcurementItem> {
            return if (yearMonth == null) SpecificationUtils.empty()
            else Specification { root, query, cb ->
                cb.greaterThanOrEqualTo(
                    root.joinOnce<ProcurementItem, Procurement>("procurement")
                        .get<OffsetDateTime>("createdDateTime"),
                    yearMonth.atDay(1).atStartOfDay()
                        .atOffset(OffsetDateTime.now(ZoneId.systemDefault()).offset)
                )
            }
        }

        fun systemServiceEquals(systemService: SystemService):Specification<ProcurementItem> {
            return SystemServiceRepository.Specifications.systemServiceEquals({ r->r.joinOnce<ProcurementItem, Procurement>("procurement").joinOnce<Procurement, ProcurementSupplier>("supplier")},systemService)
        }
    }

}

interface CustomProcurementItemRepository {
    fun sumBySupplier(
        supplierId: Int,
        request: ProcurementSupplierPaymentRecordController.QueryProcurementPaymentRecordRequest,
        systemService: SystemService
    ): BigDecimal
}

class CustomProcurementItemRepositoryImpl : CustomProcurementItemRepository {

    @PersistenceContext
    private lateinit var entityManager: EntityManager

    override fun sumBySupplier(
        supplierId: Int,
        request: ProcurementSupplierPaymentRecordController.QueryProcurementPaymentRecordRequest,
        systemService: SystemService
    ): BigDecimal {

        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery()
        val root = query.from(ProcurementReceiptItem::class.java)
        val unitJoin = root.joinOnce<ProcurementReceiptItem, GoodsUnit>("unit", JoinType.LEFT)
        val amountWithBasicUnit = criteriaBuilder.prod(
            criteriaBuilder.coalesce(root.get<Int>("practicalAmount"),0),
            criteriaBuilder.function(
                "greatest",
                Int::class.java,
                criteriaBuilder.coalesce(unitJoin.get<Int>("basicMultiple"), 1),
                criteriaBuilder.literal(1)
            ),
        )
        query.select(
            criteriaBuilder.sum<BigDecimal>(
                criteriaBuilder.prod(
                    root.get<BigDecimal>("purchasePrice"), amountWithBasicUnit.`as`(
                        BigDecimal::class.java
                    )
                )
            )
        )


        query.where(
            Specification.allOf(
                ProcurementReceiptItemRepository.Specifications.systemServiceEqual(systemService),
                ProcurementReceiptItemRepository.Specifications.supplierIdEqual(supplierId),
                ProcurementReceiptItemRepository.Specifications.createdDateTimeBefore(request.endPaidDateTime),
                ProcurementReceiptItemRepository.Specifications.procurementValidated(),
            ).toPredicate(root, query, criteriaBuilder)
        )

        return (entityManager.createQuery(query).singleResult as BigDecimal?) ?: BigDecimal.ZERO
    }
}
